﻿using DAL;
using Model;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace MainOP
{
    public partial class FormDataSelectRepair : Form
    {
        public FormDataSelectRepair()
        {
            InitializeComponent();
        }
        List<ProductData> objProductData = new List<ProductData>();
        private void button1_Click(object sender, EventArgs e)
        {
            //查询前先清空数据集
            objProductData.Clear();
            //清空表格再查询
            while (this.dataGridView1.Rows.Count != 1)
            {
                this.dataGridView1.DataSource = null;
            }
            string sql = "select [返修完成时间], [内部码], [返修操作员],[返修零件名称], [返修扫码内容] from repair where [返修完成时间] Between '{0}' and '{1}' order by [返修完成时间] ASC";
            sql = string.Format(sql, dateTimePicker1.Text, dateTimePicker2.Text);
            SqlDataReader objReader = SQLHelp.GetReader(sql);
            //循环写入数据
            while (objReader.Read())
            {
                ProductData productData = new ProductData();
                try
                {
                    productData.返修完成时间 = Convert.ToDateTime(objReader["返修完成时间"]);
                }
                catch
                { }
                productData.内部码 = objReader["内部码"].ToString();
                productData.返修操作员 = objReader["返修操作员"].ToString();
                productData.返修零件名称 = objReader["返修零件名称"].ToString();
                productData.返修扫码内容 = objReader["返修扫码内容"].ToString();
                objProductData.Add(productData);
            }
            
            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.DataSource = objProductData;
            objReader.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //查询前先清空数据集
            objProductData.Clear();
            //清空表格再查询
            while (this.dataGridView1.Rows.Count != 1)
            {
                this.dataGridView1.DataSource = null;
            }
            string sql = "select * from productData where concat([产品下线时间], [产品型号], [总成码],[序列号], [总结果], [OP10螺钉数1], [OP10螺钉数2], [OP20螺钉数1], [OP20螺钉数2], [OP30螺钉数1], [OP30螺钉数2], [OP40螺钉数1], [OP40螺钉数2], [OP50螺钉数1], [OP50螺钉数2],[OP60螺钉数1], [OP60螺钉数2], [OP70螺钉数1], [OP70螺钉数2], [OP80螺钉数1], [OP80螺钉数2]) like '%{0}%'";
            sql = string.Format(sql, textBox1.Text);
            SqlDataReader objReader = SQLHelp.GetReader(sql);
            //循环写入数据
            while (objReader.Read())
            {
                ProductData productData = new ProductData();
                try
                {
                    productData.产品下线时间 = Convert.ToDateTime(objReader["产品下线时间"]);
                }
                catch
                {

                }
                productData.产品型号 = objReader["产品型号"].ToString();
                productData.序列号 = objReader["序列号"].ToString();
                productData.总成码 = objReader["总成码"].ToString();
                productData.总结果 = objReader["总结果"].ToString();
                productData.OP10螺钉数1 = objReader["OP10螺钉数1"].ToString();
                productData.OP10螺钉数2 = objReader["OP10螺钉数2"].ToString();
                productData.OP20螺钉数1 = objReader["OP20螺钉数1"].ToString();
                productData.OP20螺钉数2 = objReader["OP20螺钉数2"].ToString();
                productData.OP30螺钉数1 = objReader["OP30螺钉数1"].ToString();
                productData.OP30螺钉数2 = objReader["OP30螺钉数2"].ToString();
                productData.OP40螺钉数1 = objReader["OP40螺钉数1"].ToString();
                productData.OP40螺钉数2 = objReader["OP40螺钉数2"].ToString();
                productData.OP50螺钉数1 = objReader["OP50螺钉数1"].ToString();
                productData.OP50螺钉数2 = objReader["OP50螺钉数2"].ToString();
                productData.OP60螺钉数1 = objReader["OP60螺钉数1"].ToString();
                productData.OP60螺钉数2 = objReader["OP60螺钉数2"].ToString();
                productData.OP70螺钉数1 = objReader["OP70螺钉数1"].ToString();
                productData.OP70螺钉数2 = objReader["OP70螺钉数2"].ToString();
                productData.OP80螺钉数1 = objReader["OP80螺钉数1"].ToString();
                productData.OP80螺钉数2 = objReader["OP80螺钉数2"].ToString();
                objProductData.Add(productData);
            }
            this.dataGridView1.AutoGenerateColumns = false;
            this.dataGridView1.DataSource = objProductData;
            objReader.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //创建Excel工作簿和工作表
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet newsheet = workbook.CreateSheet("追溯记录");

            //创建Excel工作表的列标题
            IRow rowHeader = newsheet.CreateRow(0);
            rowHeader.CreateCell(0, CellType.String).SetCellValue("返修完成时间");
            rowHeader.CreateCell(1, CellType.String).SetCellValue("内部码");
            rowHeader.CreateCell(2, CellType.String).SetCellValue("返修操作员");
            rowHeader.CreateCell(3, CellType.String).SetCellValue("返修零件名称");
            rowHeader.CreateCell(4, CellType.String).SetCellValue("返修扫码内容");
            
            
            //循环创建Excel行，并填充数据
            for (int i = 0; i < objProductData.Count; i++)
            {
                IRow row = newsheet.CreateRow(i + 1);
                row.CreateCell(0, CellType.String).SetCellValue(objProductData[i].返修完成时间.ToString());
                row.CreateCell(1, CellType.String).SetCellValue(objProductData[i].内部码);
                row.CreateCell(2, CellType.String).SetCellValue(objProductData[i].返修操作员);
                row.CreateCell(3, CellType.String).SetCellValue(objProductData[i].返修零件名称);
                row.CreateCell(4, CellType.String).SetCellValue(objProductData[i].返修扫码内容);
                
                
            }

            //选择文件位置
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.InitialDirectory = @"C:\Users\SpringRain\Desktop";
            sfd.Title = "请选择要保存的文件";
            sfd.Filter = "Excel表格|*.xlsx";
            sfd.ShowDialog(this);
            string path = sfd.FileName;

            //将输出流写入Excel工作簿
            try
            {
                using (FileStream fSwrite = File.OpenWrite(path))
                {
                    workbook.Write(fSwrite);
                    MessageBox.Show("保存成功");
                }
            }
            catch
            {

            }
        }
    }
}
